-- EXEC SP_Update_Distributor_Inventory 30

ALTER PROC SP_Update_Distributor_Inventory( @CustomerShippingId INT )AS
BEGIN

	DECLARE @CUSTOMER_ID INT
	DECLARE @IS_DISTRIBUTOR BIT
	
	SELECT @CUSTOMER_ID = SO.Customer_Id FROM CUSTOMER_SHIPPING_TABLE CST, SALES_ORDER SO WHERE CST.SalesOrder_Id = SO.SalesOrder_Id AND CustomerShipping_ID = @CustomerShippingId
	
	SELECT @IS_DISTRIBUTOR = C_Is_Distributor FROM CUSTOMER WHERE Customer_Id = @CUSTOMER_ID

	IF ( @IS_DISTRIBUTOR  = 1 )
	BEGIN
		
		INSERT INTO [Distributor_Inventory_lot]([Customer_Id], [CSLIT_Id], [Lot_Id], [Lot_Number], [ProcessStep_ID], [Qty_In], [Date_In], [Date_code])
		SELECT @CUSTOMER_ID,CSLIT.CSLIT_ID, CSLNT.Lot_Id, LT.Lot_Number, LT.ProcessStep_ID, CSLNT.CSLNT_Qty_Pull, CST.CST_Ship_Date, LT.Lot_Date_Code FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT, LOT_TABLE LT
		WHERE CST.CustomerShipping_ID = CSLIT.CustomerShipping_ID AND CSLIT.CSLIT_ID = CSLNT.CSLIT_ID AND CSLNT.Lot_ID = LT.Lot_ID
		AND CST.CUSTOMERSHIPPING_ID = @CustomerShippingId
	END

END